﻿<%@ WebHandler Language="C#" Class="ZSWorkflownameReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: 施工单报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.25
 **/

public class ZSWorkflownameReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetail":
                        r = getDetail(jObject);//查询明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,wfb.workflowname,wf.ly2modle_node engineno,mfg.mfgordername,pf.productfamilyname,wf.process_no xiadaDate,
       wf.createdatetime,e.fullname,ep.finishdate,
       NVL(ep.enginecontainerid,ep.containerid) containerid,wf.workflowid,
       --调用存储函数查询工时总额、完成进度
       compute_sgd_finish_rate(wf.workflowid,NVL(ep.enginecontainerid,ep.containerid),ep.finishdate) FinishRate,
       compute_sgd_worktime(wf.workflowid) durationperunitsum
FROM workflow wf 
LEFT JOIN workflowbase wfb ON wf.workflowbaseid = wfb.workflowbaseid 
LEFT JOIN enginepartplan ep ON wf.workflowid=ep.workflowid 
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN Workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
LEFT JOIN employee e ON e.employeeid = wf.createemployeeid 
INNER JOIN workflowstep ws ON ws.workflowid = wf.workflowid
LEFT JOIN factory f ON f.factoryid = wf.factoryid
WHERE wf.workflowtype ='施工工艺' AND wf.ly2modle_node IS NOT NULL AND ws.workflowstepid IS NOT NULL ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND wf.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //施工单号
        if (jObject["workflowname"] != null && jObject["workflowname"].ToString() != "")
        {
            sql += string.Format(" AND wfb.workflowname = '{0}'", jObject["workflowname"].ToString());
        }

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND wf.ly2modle_node = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND pf.productfamilyname LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        //施工单创建人
        if (jObject["creater"] != null && jObject["creater"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname LIKE '%{0}%'", jObject["creater"].ToString());
        }

        //施工单创建时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND wf.createdatetime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND wf.createdatetime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //施工单完工时间
        if (jObject["finishdate"] != null && jObject["finishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["finishdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ep.finishdate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND ep.finishdate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //生成状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "已生成")
            {
                sql += " AND NVL(ep.enginecontainerid,ep.containerid) IS NOT NULL";
            }
            else
            {
                sql += " AND NVL(ep.enginecontainerid,ep.containerid) IS NULL";
            }
        }

        sql += @"
GROUP BY f.factoryname,wfb.workflowname,wf.ly2modle_node,mfg.mfgordername,pf.productfamilyname,wf.process_no,wf.createdatetime,
         e.fullname,ep.finishdate,NVL(ep.enginecontainerid,ep.containerid),wf.workflowid";

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询明细
    Result getDetail(JObject jObject)
    {
        string sql = @"
SELECT t.sequence,t.wsdescription,t.workflowstepname,t.description,t.teamname,t.dispatchdate,t.dispatchperson,
       t.reportdate,t.reportperson,t.checkdate,t.checkperson,t.status,t.durationperunit,
       listagg(t.workers,'、') WITHIN GROUP (ORDER BY t.workers) workers
FROM (
    SELECT wfs.sequence,wfs.description wsdescription,wfs.workflowstepname,NVL(s.description,'--') description,NVL(t.teamname, w.workcentername) teamname,
           csd.createdate dispatchdate,e.fullname dispatchperson,
           csw.createdate reportdate,e2.fullname reportperson,cki.createdate checkdate,e3.fullname checkperson,
           CASE WHEN cki.createdate IS NOT NULL THEN '已检验'
                WHEN cki.createdate IS NULL AND csd.createdate IS NULL THEN '待派工'
                WHEN cki.createdate IS NULL AND csd.createdate IS NOT NULL AND csw.createdate IS NULL THEN '待报工'
                WHEN cki.createdate IS NULL AND csd.createdate IS NOT NULL AND csw.createdate IS NOT NULL THEN '待检验' 
           END status,
           CASE WHEN s.stepinfoid IS NOT NULL THEN NVL(s.durationperunit,0) 
                ELSE nvl(sd.durationperunit,0) 
           END durationperunit,
           NVL(e5.fullname,e4.fullname) workers
    FROM workflowstep wfs
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = wfs.workflowstepid
    LEFT JOIN team t ON wfs.teamid = t.teamid
    LEFT JOIN workcenter w ON wfs.workcenterid = w.workcenterid
    LEFT JOIN stepinfo s ON wfs.workflowstepid = s.workflowstepid
    LEFT JOIN stepinfobase sib ON sib.stepinfobaseid = s.stepinfobaseid
    LEFT JOIN containerstepdispatch csd ON csd.containerid = '{0}' AND NVL(csd.stepinfoid,csd.workflowstepid) = NVL(s.stepinfoid,wfs.workflowstepid) AND csd.isuse = 0
    LEFT JOIN containerstepworkreport csw ON csw.containerid = '{0}' AND NVL(csw.stepinfoid,csw.workflowstepid) = NVL(s.stepinfoid,wfs.workflowstepid) AND csw.isuse = 0
    LEFT JOIN containercheckinfo cki ON cki.containerid = '{0}' AND NVL(cki.stepinfoid,cki.workflowstepid) = NVL(s.stepinfoid,wfs.workflowstepid) AND cki.isuse = 0
    LEFT JOIN employee e ON e.employeeid = csd.employeeid
    LEFT JOIN employee e2 ON e2.employeeid = csw.workerid
    LEFT JOIN employee e3 ON e3.employeeid = cki.checkerid
    LEFT JOIN containerstepdispatchdetail_zs csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
    LEFT JOIN employee e4 ON e4.employeeid = csd.workerid
    LEFT JOIN employee e5 ON e5.employeeid = csdd.workerid
    WHERE wfs.workflowid = '{1}' 
    ORDER BY wfs.sequence
) t
GROUP BY t.sequence,t.wsdescription,t.workflowstepname,t.description,t.teamname,t.dispatchdate,t.dispatchperson,
         t.reportdate,t.reportperson,t.checkdate,t.checkperson,t.status,t.durationperunit";

        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowid"].ToString());

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT t.factoryname,t.workflowname,t.engineno,t.mfgordername,t.productfamilyname,t.xiadaDate,t.createdatetime,
    t.fullname,t.finishdate,t.FinishRate,t.durationperunitsum,
    wfs.sequence,wfs.workflowstepname,wfs.description wsdescription,NVL(s.description,'--') description,
    NVL(t.teamname, w.workcentername) teamname,csd.createdate dispatchdate,e.fullname dispatchperson,
    csw.createdate reportdate,e2.fullname reportperson,cki.createdate checkdate,e3.fullname checkperson,
    CASE WHEN cki.createdate IS NOT NULL THEN '已检验'
         WHEN cki.createdate IS NULL AND csd.createdate IS NULL THEN '待派工'
         WHEN cki.createdate IS NULL AND csd.createdate IS NOT NULL AND csw.createdate IS NULL THEN '待报工'
         WHEN cki.createdate IS NULL AND csd.createdate IS NOT NULL AND csw.createdate IS NOT NULL THEN '待检验' END status,
    CASE WHEN s.stepinfoid IS NOT NULL THEN NVL(s.durationperunit,0) ELSE nvl(sd.durationperunit,0) END durationperunit
FROM
(
    SELECT f.factoryname,wfb.workflowname,wf.ly2modle_node engineno,mfg.mfgordername,pf.productfamilyname,wf.process_no xiadaDate,
        wf.createdatetime,e.fullname,ep.finishdate,NVL(ep.enginecontainerid,ep.containerid) containerid,wf.workflowid,
        compute_sgd_finish_rate(wf.workflowid,NVL(ep.enginecontainerid,ep.containerid),ep.finishdate) FinishRate,compute_sgd_worktime(wf.workflowid) durationperunitsum
    FROM workflow wf 
    LEFT JOIN workflowbase wfb ON wf.workflowbaseid = wfb.workflowbaseid 
    LEFT JOIN enginepartplan ep ON wf.workflowid=ep.workflowid 
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN Workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
    LEFT JOIN employee e ON e.employeeid = wf.createemployeeid 
    INNER JOIN workflowstep ws ON ws.workflowid = wf.workflowid 
    LEFT JOIN factory f ON f.factoryid = wf.factoryid
    WHERE wf.workflowtype ='施工工艺' AND wf.ly2modle_node IS NOT NULL AND ws.workflowstepid IS NOT NULL ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND wf.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //施工单号
        if (jObject["workflowname"] != null && jObject["workflowname"].ToString() != "")
        {
            sql += string.Format(" AND wfb.workflowname = '{0}'", jObject["workflowname"].ToString());
        }

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND wf.ly2modle_node = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND pf.productfamilyname LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        //施工单创建人
        if (jObject["creater"] != null && jObject["creater"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname LIKE '%{0}%'", jObject["creater"].ToString());
        }

        //施工单创建时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND wf.createdatetime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND wf.createdatetime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //施工单完工时间
        if (jObject["finishdate"] != null && jObject["finishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["finishdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ep.finishdate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND ep.finishdate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //生成状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "已生成")
            {
                sql += " AND NVL(ep.enginecontainerid,ep.containerid) IS NOT NULL";
            }
            else
            {
                sql += " AND NVL(ep.enginecontainerid,ep.containerid) IS NULL";
            }
        }

        sql += @"
    GROUP BY f.factoryname,wfb.workflowname,wf.ly2modle_node,mfg.mfgordername,pf.productfamilyname,wf.process_no,wf.createdatetime,
             e.fullname,ep.finishdate,NVL(ep.enginecontainerid,ep.containerid),wf.workflowid
) t
LEFT JOIN workflowstep wfs ON wfs.workflowid = t.workflowid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = wfs.workflowstepid
LEFT JOIN team t ON wfs.teamid = t.teamid
LEFT JOIN workcenter w ON wfs.workcenterid = w.workcenterid
LEFT JOIN stepinfo s ON wfs.workflowstepid = s.workflowstepid
LEFT JOIN stepinfobase sib ON sib.stepinfobaseid = s.stepinfobaseid
LEFT JOIN containerstepdispatch csd ON csd.containerid = t.containerid AND NVL(csd.stepinfoid,csd.workflowstepid) = NVL(s.stepinfoid,wfs.workflowstepid) AND csd.isuse = 0
LEFT JOIN containerstepworkreport csw ON csw.containerid = t.containerid AND NVL(csw.stepinfoid,csw.workflowstepid) =  NVL(s.stepinfoid,wfs.workflowstepid) AND csw.isuse = 0
LEFT JOIN containercheckinfo cki ON cki.containerid = t.containerid AND NVL(cki.stepinfoid,cki.workflowstepid) = NVL(s.stepinfoid,wfs.workflowstepid) AND cki.isuse = 0
LEFT JOIN employee e ON e.employeeid = csd.employeeid
LEFT JOIN employee e2 ON e2.employeeid = csw.workerid
LEFT JOIN employee e3 ON e3.employeeid = cki.checkerid
ORDER BY t.workflowname,wfs.sequence
";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns["factoryname"].Caption = "分厂";
        dt.Columns["workflowname"].Caption = "施工单号";
        dt.Columns["engineno"].Caption = "发动机号";
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["productfamilyname"].Caption = "机型";
        dt.Columns["xiadaDate"].Caption = "下达时间";
        dt.Columns["createdatetime"].Caption = "创建时间";
        dt.Columns["fullname"].Caption = "创建人";
        dt.Columns["finishdate"].Caption = "完工时间";
        dt.Columns["FinishRate"].Caption = "完成进度";
        dt.Columns["durationperunitsum"].Caption = "定额总工时";
        dt.Columns["sequence"].Caption = "工序号";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["wsdescription"].Caption = "内容描述";
        dt.Columns["description"].Caption = "工步";
        dt.Columns["teamname"].Caption = "组";
        dt.Columns["dispatchdate"].Caption = "派工时间";
        dt.Columns["dispatchperson"].Caption = "派工人";
        dt.Columns["reportdate"].Caption = "报工时间";
        dt.Columns["reportperson"].Caption = "报工人";
        dt.Columns["checkdate"].Caption = "检验时间";
        dt.Columns["checkperson"].Caption = "检验员";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["durationperunit"].Caption = "定额工时";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "施工单统计报表");
    }
}